# -*- coding: utf-8 -*-

import pymysql
# from project_setting import get_project_settings  # 导入settings配置

import configparser



class DBHelper():
    #读取settings中的配置，自行修改代码进行操作

    def __init__(self):
        config = configparser.ConfigParser()
        config.read("..config.ini")
        self.host = config['mysql']['host']
        self.port = config['mysql']['host']
        self.user = config['mysql']['user']
        self.passwd = config['mysql']['password']
        self.db = config['mysql']['db']

        # self.host = '127.0.0.1'
        # self.port = 3306
        # self.user = 'root'
        # self.passwd = 'root'
        # self.db = 'PDF_graduate_student_enroll'

    # 连接到mysql，不是连接到具体的数据库
    def connectMysql(self):
        conn = pymysql.connect(host=self.host,
                               port=self.port,
                               user=self.user,
                               passwd=self.passwd,
                               # db=self.db,不指定数据库名
                               charset='utf8')  # 要指定编码，否则中文可能乱码
        return conn

    # 连接到具体的数据库（settings中设置的MYSQL_DBNAME）
    def connectDatabase(self):
        conn = pymysql.connect(host=self.host,
                               port=self.port,
                               user=self.user,
                               passwd=self.passwd,
                               db=self.db,
                               charset='utf8')  # 要指定编码，否则中文可能乱码
        return conn

        # 创建数据库

    def createDatabase(self):
        '''因为创建数据库直接修改settings中的配置MYSQL_DBNAME即可，所以就不要传sql语句了'''
        conn = self.connectMysql()  # 连接数据库


        cur = conn.cursor()

        sql = "create database if not exists " + self.db

        cur.execute(sql)  # 执行sql语句
        # conn.commit()
        cur.close()
        conn.close()

    # 创建表
    def createTable(self, sql):
        conn = self.connectDatabase()

        cur = conn.cursor()
        cur.execute(sql)
        cur.close()
        conn.close()

    # 插入数据
    def insert(self, sql, *params):  # 注意这里params要加*,因为传递过来的是元组，*表示参数个数不定
        conn = self.connectDatabase()

        cur = conn.cursor()
        cur.execute(sql, params)
        conn.commit()  # 注意要commit
        cur.close()
        conn.close()

    # 更新数据
    def update(self, sql, *params):
        conn = self.connectDatabase()

        cur = conn.cursor()
        cur.execute(sql, params)
        conn.commit()  # 注意要commit
        cur.close()
        conn.close()

    # 查询数据
    def select(self, sql):
        conn = self.connectDatabase()
        try:

            cur = conn.cursor()
            cur.execute(sql)
            result = cur.fetchall()
            conn.commit()  # 注意要commit

            return result
        except:
            print("Error: unable to fecth data")

        cur.close()
        conn.close()

    # 删除数据
    def delete(self, sql, *params):
        conn = self.connectDatabase()

        cur = conn.cursor()
        cur.execute(sql, params)
        conn.commit()
        cur.close()
        conn.close()


#测试DBHelper的类#


class CheckDBHelper():

    def __init__(self):
        self.dbHelper = DBHelper()

    # 测试创建数据库（settings配置文件中的MYSQL_DBNAME,直接修改settings配置文件即可）
    def testCreateDatebase(self):
        self.dbHelper.createDatabase()
        # 测试创建表

    def testCreateTable(self):
        # sql = "create table if not EXISTS testtable(id int primary key auto_increment,name varchar(50),url varchar(200))"
        # self.dbHelper.createTable(sql)
        sql = "create table if not EXISTS students_enroll_2(id int primary key auto_increment," \
              "stu_name varchar(20),"\
              "stu_gender varchar(10),"\
              "stu_id varchar(50)," \
              "original_school varchar(50)," \
              "original_major varchar(50),"\
              "target_school varchar(50)," \
              "target_school_id varchar(20)," \
              "target_academy varchar(50)," \
              "target_academy_id varchar(20)," \
              "target_major varchar(50)," \
              "target_major_id varchar(20)," \
              "study_direction varchar(50)," \
              "study_direction_id varchar(20)," \
              "if_no_exam varchar(10)," \
              "recommend_type varchar(20)," \
              "study_type varchar(20)," \
              "study_source varchar(20)," \
              "politic_score varchar(15)," \
              "forign_score varchar(15)," \
              "major1_score varchar(15)," \
              "major2_score varchar(15)," \
              "first_score varchar(20)," \
              "second_writen varchar(20)," \
              "second_oral varchar(20)," \
              "second_interview varchar(20)," \
              "second_score varchar(20)," \
              "final_score varchar(20)," \
              "years varchar(10)," \
              "tutor_name varchar(20)," \
              "remark varchar(500)," \
              "file_type varchar(50)," \
              "from_file varchar(200)," \
              "from_table varchar(50))"
        self.dbHelper.createTable(sql)

    # 测试插入
    def testInsert(self):
        sql = "insert into testtable(name,url) values(%s,%s)"
        params = ("test", "test")
        self.dbHelper.insert(sql, *params)  # *表示拆分元组，调用insert（*params）会重组成元组

    def testUpdate(self):
        sql = "update testtable set name=%s,url=%s where id=%s"
        params = ("update", "update", "1")
        self.dbHelper.update(sql, *params)

    #测试查询
    def testselect(self):
        urls=[]
        sql="select * from notification_jxufe where " \
            "notification_title like '%招生简章%'and " \
            "notification_title like'%2018%'and " \
            "notification_title like '%硕士%'"
        url=self.dbHelper.select(sql)
        for row in url:
            # url2="'"+row[3]+"'"+","
            url2=row[3]
            urls.append(url2)
        print(urls)


    def testDelete(self):
        sql = "delete from testtable where id=%s"
        params = ("1")
        self.dbHelper.delete(sql, *params)


if __name__ == "__main__":
    testDBHelper = CheckDBHelper()
    # testDBHelper.testCreateDatebase()  #执行测试创建数据库
    testDBHelper.testCreateTable()  # 执行测试创建表
    #  testDBHelper.testselect()  # 执行测试创建表
    # testDBHelper.testInsert()          #执行测试插入数据
    # testDBHelper.testUpdate()          #执行测试更新数据
    # testDBHelper.testDelete()          #执行测试删除数据